﻿using System;
using System.Collections.Generic;
using System.Text;
using DotNet.Model;
using DotNet.Common;
using DotNet.Data.Factory;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace DotNet.Data
{
    public class UserDal
    {
        IDBHelp db = DBHelp.Create();
        Hashtable htSyn = Hashtable.Synchronized(new Hashtable());
        StringBuilder strSql = new StringBuilder();

        #region 是否存在该记录
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int userID)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(1) from [User] where userID=@userID");
            htSyn.Add("userID", userID);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 用户登录---郑同飞
        /// <summary>
        ///  用户登录---郑同飞
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="userPwd"></param>
        public bool Login(string userName,string userPwd) 
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(1) from [User] where userName=@username and userPwd=@userpwd and userState=1");
            htSyn.Add("@username", userName);
            htSyn.Add("@userpwd", userPwd);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 根据用户名和密码获取用户实体---郑同飞
        /// <summary>
        /// 根据用户名和密码获取用户实体---郑同飞
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="userPwd"></param>
        /// <returns></returns>
        public User GetModel(string userName,string userPwd) 
        {
            htSyn.Clear();
            strSql.Remove(0,strSql.Length);
            strSql.Append("select userID,userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData from [User] ");
            strSql.Append(" where userName=@username and userPwd=@userpwd");
            htSyn.Add("@username",userName);
            htSyn.Add("@userpwd",userPwd);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
            User User = new User();
            if (dt.Rows.Count > 0)
            {
                
                User.userID = Convert.ToInt32(dt.Rows[0]["userID"]);
                User.userNumber = Convert.ToInt32(dt.Rows[0]["userNumber"]);
                User.userName = dt.Rows[0]["userName"].ToString();
                User.userPwd = dt.Rows[0]["userPwd"].ToString();
                User.userXM = dt.Rows[0]["userXM"].ToString();
                User.userSex = dt.Rows[0]["userSex"].ToString();
                User.userBirth = dt.Rows[0]["userBirth"].ToString() == "" ? Convert.ToDateTime("00:00:00") : Convert.ToDateTime(dt.Rows[0]["userBirth"].ToString());
                User.uerQQ = dt.Rows[0]["uerQQ"].ToString();
                User.userTel = dt.Rows[0]["userTel"].ToString();
                User.userPhone = dt.Rows[0]["userPhone"].ToString();
                User.userEmail = dt.Rows[0]["userEmail"].ToString();
                User.userAddress = dt.Rows[0]["userAddress"].ToString();
                User.userMark = dt.Rows[0]["userMark"].ToString();
                User.userState = Convert.ToBoolean(dt.Rows[0]["userState"]);
                User.userData = Convert.ToDateTime(dt.Rows[0]["userData"]);
            }
           return User;
            
        }
        #endregion

        #region 获得用户信息数据列表
        /// <summary>
        /// 获得用户信息数据列表
        /// </summary>
        /// <returns></returns>
        public IList<User> GetModel()
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select userID,userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData from [User] ");
            return getUsersBySql(strSql.ToString());
        }
        #endregion

        #region 根据用户信息号查询实体
        /// <summary>
        /// 根据用户信息号查询实体
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public User GetModel(int userID)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select userID,userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData from [User] ");
            strSql.Append(" where userID=@userID ");
            htSyn.Add("userID", userID);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
            User User = new User();
            if (dt.Rows.Count > 0)
            {
               
                User.userID = Convert.ToInt32(dt.Rows[0]["userID"]);
                User.userNumber = Convert.ToInt32(dt.Rows[0]["userNumber"]);
                User.userName = dt.Rows[0]["userName"].ToString();
                User.userPwd = dt.Rows[0]["userPwd"].ToString();
                User.userXM = dt.Rows[0]["userXM"].ToString();
                User.userSex = dt.Rows[0]["userSex"].ToString();
                User.userBirth = dt.Rows[0]["userBirth"].ToString() == "" ? Convert.ToDateTime("00:00:00") : Convert.ToDateTime(dt.Rows[0]["userBirth"].ToString());
                User.uerQQ = dt.Rows[0]["uerQQ"].ToString();
                User.userTel = dt.Rows[0]["userTel"].ToString();
                User.userPhone = dt.Rows[0]["userPhone"].ToString();
                User.userEmail = dt.Rows[0]["userEmail"].ToString();
                User.userAddress = dt.Rows[0]["userAddress"].ToString();
                User.userMark = dt.Rows[0]["userMark"].ToString();
                User.userState = Convert.ToBoolean(dt.Rows[0]["userState"]);
                User.userData = Convert.ToDateTime(dt.Rows[0]["userData"]);
            }
            return User;

        }
        #endregion

        #region 根据用户名查询实体
        /// <summary>
        /// 根据用户名查询实体
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public User GetModel(string userName)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select userID,userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData from [User] ");
            strSql.Append(" where userName=@username ");
            htSyn.Add("username", userName);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
            User User = new User();
            if (dt.Rows.Count > 0)
            {

                User.userID = Convert.ToInt32(dt.Rows[0]["userID"]);
                User.userNumber = Convert.ToInt32(dt.Rows[0]["userNumber"]);
                User.userName = dt.Rows[0]["userName"].ToString();
                User.userPwd = dt.Rows[0]["userPwd"].ToString();
                User.userXM = dt.Rows[0]["userXM"].ToString();
                User.userSex = dt.Rows[0]["userSex"].ToString();
                User.userBirth = dt.Rows[0]["userBirth"].ToString() == "" ? Convert.ToDateTime("00:00:00") : Convert.ToDateTime(dt.Rows[0]["userBirth"].ToString());
                User.uerQQ = dt.Rows[0]["uerQQ"].ToString();
                User.userTel = dt.Rows[0]["userTel"].ToString();
                User.userPhone = dt.Rows[0]["userPhone"].ToString();
                User.userEmail = dt.Rows[0]["userEmail"].ToString();
                User.userAddress = dt.Rows[0]["userAddress"].ToString();
                User.userMark = dt.Rows[0]["userMark"].ToString();
                User.userState = Convert.ToBoolean(dt.Rows[0]["userState"]);
                User.userData = Convert.ToDateTime(dt.Rows[0]["userData"]);
            }
            return User;

        }
        #endregion

        #region 根据email获得实体---ztf
        /// <summary>
        /// 根据email获得实体---ztf
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public User GetModelByEmail(string email)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select userID,userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData from [User] ");
            strSql.Append(" where userEmail=@email ");
            htSyn.Add("email", email);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
            User User = new User();
            if (dt.Rows.Count > 0)
            {

                User.userID = Convert.ToInt32(dt.Rows[0]["userID"]);
                User.userNumber = Convert.ToInt32(dt.Rows[0]["userNumber"]);
                User.userName = dt.Rows[0]["userName"].ToString();
                User.userPwd = dt.Rows[0]["userPwd"].ToString();
                User.userXM = dt.Rows[0]["userXM"].ToString();
                User.userSex = dt.Rows[0]["userSex"].ToString();
                User.userBirth = dt.Rows[0]["userBirth"].ToString() == "" ? Convert.ToDateTime("00:00:00") : Convert.ToDateTime(dt.Rows[0]["userBirth"].ToString());
                User.uerQQ = dt.Rows[0]["uerQQ"].ToString();
                User.userTel = dt.Rows[0]["userTel"].ToString();
                User.userPhone = dt.Rows[0]["userPhone"].ToString();
                User.userEmail = dt.Rows[0]["userEmail"].ToString();
                User.userAddress = dt.Rows[0]["userAddress"].ToString();
                User.userMark = dt.Rows[0]["userMark"].ToString();
                User.userState = Convert.ToBoolean(dt.Rows[0]["userState"]);
                User.userData = Convert.ToDateTime(dt.Rows[0]["userData"]);
            }
            return User;
        }
        #endregion

        #region 修改用户信息
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="User"></param>
        /// <returns></returns>
        public bool Update(User User)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("update [User] set ");
            strSql.Append("userNumber=@userNumber,");
            strSql.Append("userName=@userName,");
            strSql.Append("userPwd=@userPwd,");
            strSql.Append("userXM=@userXM,");
            strSql.Append("userSex=@userSex,");
            strSql.Append("userBirth=@userBirth,");
            strSql.Append("userTel=@userTel,");
            strSql.Append("uerQQ=@uerQQ,");
            strSql.Append("userPhone=@userPhone,");
            strSql.Append("userEmail=@userEmail,");
            strSql.Append("userAddress=@userAddress,");
            strSql.Append("userMark=@userMark,");
            strSql.Append("userState=@userState,");
            strSql.Append("userData=@userData");
            strSql.Append(" where userID=@userID ");
            htSyn.Add("@userNumber", User.userNumber);
            htSyn.Add("@userName", User.userName);
            htSyn.Add("@userPwd", User.userPwd);
            htSyn.Add("@userXM", User.userXM);
            htSyn.Add("@userSex", User.userSex);
            htSyn.Add("@userBirth", User.userBirth);
            htSyn.Add("@userTel", User.userTel);
            htSyn.Add("@uerQQ", User.uerQQ);
            htSyn.Add("@userPhone", User.userPhone);
            htSyn.Add("@userEmail", User.userEmail);
            htSyn.Add("@userAddress", User.userAddress);
            htSyn.Add("@userMark", User.userMark);
            htSyn.Add("@userState", User.userState);
            htSyn.Add("@userData", User.userData);
            htSyn.Add("@userID", User.userID);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 删除用户信息根据用户信息号
        /// <summary>
        /// 删除用户信息根据用户信息号
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public bool Delete(int userID)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from [User] ");
            strSql.Append(" where userID=@userID ");
            htSyn.Add("userID", userID);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 添加用户信息
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="User"></param>
        /// <returns></returns>
        public bool Insert(User User)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("insert into [User](");
            strSql.Append("userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData)");
            strSql.Append(" values (");
            strSql.Append("@userNumber,@userName,@userPwd,@userXM,@userSex,@userBirth,@userTel,@uerQQ,@userPhone,@userEmail,@userAddress,@userMark,@userState,@userData)");          
            htSyn.Add("@userNumber", User.userNumber);
            htSyn.Add("@userName", User.userName);
            htSyn.Add("@userPwd", User.userPwd);
            htSyn.Add("@userXM", User.userXM);
            htSyn.Add("@userSex", User.userSex);
            htSyn.Add("@userBirth", User.userBirth);
            htSyn.Add("@userTel", User.userTel);
            htSyn.Add("@uerQQ", User.uerQQ);
            htSyn.Add("@userPhone", User.userPhone);
            htSyn.Add("@userEmail", User.userEmail);
            htSyn.Add("@userAddress", User.userAddress);
            htSyn.Add("@userMark", User.userMark);
            htSyn.Add("@userState", User.userState);
            htSyn.Add("@userData", User.userData);           
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 检测手机号码是否重复---郑同飞
        /// <summary>
        /// 检测手机号码是否重复---郑同飞
        /// </summary>
        /// <param name="tel"></param>
        /// <returns></returns>
        public bool checkPhone(string phone)
        {
            htSyn.Clear();
            strSql.Remove(0,strSql.Length);
            strSql.Append("select count(1) from [User] where userPhone=@userphone");
            htSyn.Add("@userphone", phone);
            return db.IsExists(strSql.ToString(),htSyn);
        }
        #endregion

        #region 检测用户是否存在---郑同飞
        /// <summary>
        /// 检测用户是否存在---郑同飞
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool checkUser(string userName)
        {
            htSyn.Clear();
            strSql.Remove(0,strSql.Length);
            strSql.Append("select count(1) from [User] where userName=@username");
            htSyn.Add("@username",userName);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 检测邮箱是否存在---郑同飞
        /// <summary>
        /// 检测邮箱是否存在---郑同飞
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public bool checkEmail(string email)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(1) from [User] where userEmail=@useremail");
            htSyn.Add("@useremail", email);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 根据SQL语句获取集合
        /// <summary>
        ///根据SQL语句获取集合
        /// </summary>
        public IList<User> getUsersBySql(string strSql)
        {
            IList<User> list = new List<User>();
            DataTable dt = db.GetDataTable(strSql);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    User User = new User();
                    User.userID = Convert.ToInt32(dr["userID"]);
                    User.userNumber = Convert.ToInt32(dr["userNumber"]);
                    User.userName = dr["userName"].ToString();
                    User.userPwd = dr["userPwd"].ToString();
                    User.userXM = dr["userXM"].ToString();
                    User.userSex = dr["userSex"].ToString();
                    User.userBirth = dt.Rows[0]["userBirth"].ToString() == "" ? Convert.ToDateTime("00:00:00") : Convert.ToDateTime(dt.Rows[0]["userBirth"].ToString());
                    User.uerQQ = dr["uerQQ"].ToString();
                    User.userTel = dr["userTel"].ToString();
                    User.userPhone = dr["userPhone"].ToString();
                    User.userEmail = dr["userEmail"].ToString();
                    User.userAddress = dr["userAddress"].ToString();
                    User.userMark = dr["userMark"].ToString();
                    User.userState = Convert.ToBoolean(dr["userState"]);
                    User.userData = Convert.ToDateTime(dr["userData"]);
                    list.Add(User);
                }
            }
            return list;
        }
        #endregion

        #region 用户分页---郑同飞
        /// <summary>
        /// 用户分页---郑同飞
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="userName"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <param name="userState"
        /// <returns></returns>
        public DataTable pagingForUser(int pageIndex,int pageSize,string userName,string email,string phone,string userState) 
        {
            strSql.Remove(0, strSql.Length);
            if (userState!="")
            {
                strSql.AppendFormat(" and userState={0}", userState);
            }            
            if (userName!="")
            {
                strSql.AppendFormat(" and userName='{0}'", userName);
            }
            if (email!="")
            {
                strSql.AppendFormat(" and userEmail='{0}'", email);
            }
            if (phone!="")
            {
                strSql.AppendFormat(" and userPhone='{0}'", phone);
            }
            return db.GetMPageDataTable(pageIndex, pageSize, "userID,userNumber,userName,userPwd,userXM,userSex,userBirth,userTel,uerQQ,userPhone,userEmail,userAddress,userMark,userState,userData", "[User]", strSql.ToString(), "userData", true);
        }
        #endregion

        #region 获取用户数量---郑同飞
        /// <summary>
        /// 获取用户数量---郑同飞
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public int getRecordCount(string userName,string email,string phone) 
        {
            strSql.Remove(0, strSql.Length);
            htSyn.Clear();
            object objCount = null;
            int i = 0;
            strSql.Append("select count(1) from [User] where 1>0 ");
            if (userName!="")
            {
                strSql.Append(" and UserName=@username");
                htSyn.Add("@username", userName);
            }
            if (email!="")
            {
                strSql.Append(" and userEmail=@useremail");
                htSyn.Add("@useremail",email);
            }
            if (phone!="")
            {
                strSql.Append(" and userPhone=@userphone");
                htSyn.Add("@userphone",phone);
            }
            objCount = db.ExecuteScalar(strSql.ToString(), htSyn);
            if (objCount!=null)
            {
                i = Convert.ToInt32(objCount);
            }
            return i;
        }
        #endregion
    }
}
